package jk;



import DBUtil.DBUtil1;
import user.Users;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDao3 {



    public static int insert(Users us) throws ClassNotFoundException, SQLException {

// 1，获取连接.
       Connection con=DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql = "insert into J_USER values(SEQ_J_USER.nextval,?,?,1)";
        //2，2创建prepareStatement对象，执行 sqL语句
       PreparedStatement ps = con.prepareStatement(sql);
        //3.给占位符进行赋值
        ps.setString(1,us.getUsername());
        ps.setString(2,us.getPassword());
        int i = ps.executeUpdate();
//3.资源释放
        DBUtil1.close();
        return i;
    }
    public static ResultSet selectbyusernameanpassword(Users us) throws ClassNotFoundException, SQLException {

        //1，获取连接.
       Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select * from J_USER where(USERNAME=? and PASSWORD=?)";
        PreparedStatement ps = con.prepareStatement(sql1);
        ps.setString(1,us.getUsername());
        ps.setString(2,us.getPassword());
        ResultSet rs=ps.executeQuery();
        DBUtil1.close();
        return rs;
    }
    //查询
    public static List<Users> result() throws ClassNotFoundException, SQLException {
        //0.创建集合
        List<Users> user=new ArrayList<>();
        //1，获取连接.
       Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql1 = "select id,username,password,state from J_USER";
        PreparedStatement ps = con.prepareStatement(sql1);
       ResultSet rs = ps.executeQuery();
        while(rs.next()){
            int id=rs.getInt("id");
            String username=rs.getString("username");
            String password=rs.getString("password");
            int state=rs.getInt("state");
          //控制台输出
          /*  System.out.println("id："+id+"，username："+username+
                    "，password："+password+"，state："+state);*/
            Users user1=new Users(id,username,password,state);
            user.add(user1);
        }
        DBUtil1.close();
        return user;
    }
    //删除
    public static int delete(Users us)throws ClassNotFoundException, SQLException{

        //1，获取连接.
       Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql2= "delete from J_USER where USERNAME=?";
       PreparedStatement ps = con.prepareStatement(sql2);
        ps.setString(1,us.getUsername());
        int i = ps.executeUpdate();
        DBUtil1.close();
        return i;
    }
    public static int update(Users us)throws ClassNotFoundException, SQLException{

        //1，获取连接.
       Connection con = DBUtil1.getConnection();
// 2执行sqL语句
//2,1创建sqL语句
        String sql3="update J_USER set PASSWORD=? where USERNAME=?";
      PreparedStatement  ps = con.prepareStatement(sql3);
        ps.setString(1,us.getPassword());
        ps.setString(2,us.getUsername());
        int i = ps.executeUpdate();
        DBUtil1.close();
        return i;

    }

}

